/**
 * 
 */
package com.mnurre.shopping.assistant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * @author michael
 *
 */
public class ListsData {
	
	private static final String TAG = "ListsData";
//	private ProductsData mProductsData;
	private Context mContext;
	private DataManager mDataManager;
	private SQLiteDatabase mDb;
	
	
	public ListsData(Context context){
		Log.d(TAG, "ListsData ctor called.");
		mContext = context;
//		mProductsData = new ProductsData(context);
	}
	
	public ListsData open() throws SQLException {
		Log.d(TAG, "open called on ListsData. Creating a new DatabaseHelper and getting a writable database.");
		mDataManager = new DataManager(mContext);
		mDb = mDataManager.getWritableDatabase();
		Log.d(TAG, "Returning this instance.");
		return this;
	}
	
	public void close(){
		Log.d(TAG, "close called on ListsData. Closing the DatabaseHelper.");
		mDataManager.close();
	}
	
	public Cursor getAllLists(){
		Log.d(TAG, "Getting all lists.");
		Cursor cursor = mDb.query(DataManager.Tables.Lists.TABLE_NAME,
				new String[] {DataManager.Tables.Lists.COLUMN_ID,
				DataManager.Tables.Lists.COLUMN_NAME },
				null, null, null, null, null);
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor getListById(Long listId){
		Log.d(TAG, "Getting list for id " + listId);
		Cursor cursor = mDb.query(DataManager.Tables.Lists.TABLE_NAME, 
				new String[] { DataManager.Tables.Lists.COLUMN_ID,  
					DataManager.Tables.Lists.COLUMN_NAME }, 
					DataManager.Tables.Lists.COLUMN_ID + "=" + listId, 
					null, null, null, null, null);
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor getProductsInList(Long listId){
		Log.d(TAG, "Getting products for list " + listId);
		String sql = "select p._id, (select name from categories where _id = categoryId) as category, name, description, price, barcode " + 
					"from products p " + 
					"left join productsByList pbl on p._id = pbl.productId " +
					"where pbl.listId=?";
		Cursor cursor = mDb.rawQuery(sql, new String[] { String.valueOf(listId) });
		if(cursor != null){
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public long addProductToListByProductId(Long listId, Long productId){
		Log.d(TAG, "Adding product " + productId + " to list.");
		ContentValues values = new ContentValues();
		values.put(DataManager.Tables.ProductsByList.COLUMN_LIST_ID, listId);
		values.put(DataManager.Tables.ProductsByList.COLUMN_PRODUCT_ID, productId);
		Log.d(TAG, "Executing query.");
		return mDb.insert(DataManager.Tables.ProductsByList.TABLE_NAME, null, values);
	}
	
	public boolean removeList(Long listId){
		Log.d(TAG, "Removing list with list id of " + listId);
		// remove all products from list
		int affected = mDb.delete(DataManager.Tables.ProductsByList.TABLE_NAME, 
				DataManager.Tables.ProductsByList.COLUMN_LIST_ID + "=?", 
				new String[] { String.valueOf(listId) });
		// delete the list
		affected = mDb.delete(DataManager.Tables.Lists.TABLE_NAME, 
				DataManager.Tables.Lists.COLUMN_ID + "=?", 
				new String[] { String.valueOf(listId) });
		return affected != 0;
	}
	
	public boolean removeProductFromList(Long listId, Long productId){
		Log.d(TAG, "Removing product " + productId + " from list " + listId);
		String sql = "delete from productsByList where listId=? and productId=?";
		Cursor cursor = mDb.rawQuery(sql, new String[] { String.valueOf(listId), String.valueOf(productId) });
		return cursor != null;
	}
	
	public long saveList(String name){
		Log.d(TAG, "Saving a new list with name " + name);
		ContentValues values = new ContentValues();
		values.put(DataManager.Tables.Lists.COLUMN_NAME, name);
		Log.d(TAG, "Executing query.");
		return mDb.insert(DataManager.Tables.Lists.TABLE_NAME, null, values);
	}
}
